/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TemporalType;
import rentaldb.Lease;
import rentaldb.Property;

/**
 *
 * @author Pop
 */
public class ReportDao {
    private EntityManager em;
    private EntityManagerFactory emf;
    
    public ReportDao(){
        try{
            emf = Persistence.createEntityManagerFactory("RentaldbPU");
            em = emf.createEntityManager();  
        }catch(Exception e){ System.out.println(e);}
    }
    public String getExpenseCostReport(Calendar from, Calendar to){
        String fromStr = from.get(Calendar.YEAR)+"-"+(from.get(Calendar.MONTH)+1)+"-"+from.get(Calendar.DATE);
        String toStr = to.get(Calendar.YEAR)+"-"+(to.get(Calendar.MONTH)+1)+"-"+to.get(Calendar.DATE);
        
        Query q = em.createNativeQuery("Select sum(amount) From expense Where duedate >= ? and duedate <= ?");
        String result = "";
        q.setParameter(1, fromStr);
        q.setParameter(2, toStr);
        Double cost = (Double)q.getSingleResult();
        cost = roundTwoDecimals(cost);
        
        q = em.createNativeQuery("Select sum(cost) From service Where dateofservice >= ? and dateofservice <= ?");
        q.setParameter(1, fromStr);
        q.setParameter(2, toStr);
        cost = cost+(Double)q.getSingleResult();
        cost = roundTwoDecimals(cost);
        
        q = em.createNativeQuery("Select sum(amount) From payment Where duedate >= ? and duedate <= ?");
        q.setParameter(1, fromStr);
        q.setParameter(2, toStr);
        Double revenue = (Double)q.getSingleResult();
        revenue = roundTwoDecimals(revenue);
        result = "The total amount received is $"+revenue+" and the total amount "
                + "spent is $"+cost+" for that time period we made $"+(roundTwoDecimals(revenue-cost))+".";
        return result;  
    }
    public List<String> getExpenseCostReportByProperty(Calendar from, Calendar to)throws Exception{
        List<String> resultList = new ArrayList<String>();
        PropertyDao pdao = new PropertyDao();
        LeaseDao ldao = new LeaseDao();
        String fromStr = from.get(Calendar.YEAR)+"-"+(from.get(Calendar.MONTH)+1)+"-"+from.get(Calendar.DATE);
        String toStr = to.get(Calendar.YEAR)+"-"+(to.get(Calendar.MONTH)+1)+"-"+to.get(Calendar.DATE);
        Calendar curDate = Calendar.getInstance();
        List<Property> propList = pdao.getAllProperties();
        for(Property p:propList){
            Query q = em.createNativeQuery("Select sum(amount) From expense Where duedate >= ? and duedate <= ? and propertyid = ?");
            String result = "";
            q.setParameter(1, fromStr);
            q.setParameter(2, toStr);
            q.setParameter(3, p.getPropertyId());
            Double cost = (Double)q.getSingleResult();
            cost = roundTwoDecimals(cost);
            
            q = em.createNativeQuery("Select sum(cost) From service Where dateofservice >= ? and dateofservice <= ? and propertyid = ?");
            q.setParameter(1, fromStr);
            q.setParameter(2, toStr);
            q.setParameter(3, p.getPropertyId());
            cost = cost+(Double)q.getSingleResult();
            cost = roundTwoDecimals(cost);
            
            Lease l = ldao.getCurrentLeaseForProperty(p, curDate);
            q = em.createNativeQuery("Select sum(amount) From payment Where duedate >= ? and duedate <= ? and leaseid = ?");
            q.setParameter(1, fromStr);
            q.setParameter(2, toStr);
            q.setParameter(3, l.getPropertyid().getPropertyId());
            Double revenue = (Double)q.getSingleResult();
            revenue = roundTwoDecimals(revenue);
            resultList.add(p.getAddress()+" "+p.getCity()+", "+p.getState()+" "+p.getZip()+":  "+"The total amount received is $"+revenue+" and\n the total amount "
                + "spent is $"+cost+" for that time period we made $"+(roundTwoDecimals(revenue-cost))+".\n");
            
        }
        return resultList;  
    }
    public double roundTwoDecimals(double d) {
            DecimalFormat twoDForm = new DecimalFormat("#.##");
        return Double.valueOf(twoDForm.format(d));
}
    
}
